package com.lu.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.lu.entity.User;
import com.lu.utils.DBTools;

public class UserServiceImpl {

	public User getUser(Map<String, Object> map) throws Exception{
		StringBuffer sql = new StringBuffer(" select * from user ");
		int count = 0;
		for(String m : map.keySet()){
			String str = (String)map.get(m);
			if(count==0){
				sql.append(" where "+m+"='"+str+"'");
			}else{
				sql.append(" and "+m+"='"+str+"'");
			}
			count++;
		}
		System.out.println(sql);
		
		DBTools db = new DBTools();
		ResultSet rs = db.getQuery(sql.toString());
		User user = new User();
		if(rs.next()){
			user.setId(rs.getLong("id"));
			user.setCreateTime(rs.getString("create_time"));
			user.setUpdateTime(rs.getString("update_time"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setStatus(rs.getInt("status"));
			user.setPhone(rs.getString("phone"));
		}
		db.close();
		return user;
	}

	public List<User> getUserList(Map<String, Object> map) throws Exception{
		StringBuffer sql = new StringBuffer(" select * from user ");
		int count = 0;
		if(map!=null){
			for(String m : map.keySet()){
				String str = (String)map.get(m);
				if(!m.equals("page")&&!m.equals("limit")){
					if(count==0){
						sql.append(" where "+m+"='"+str+"' ");
					}else{
						sql.append(" and "+m+"='"+str+"' ");
					}
					count++;
				}
			}
			
			if(map.get("page")!=null&&map.get("limit")!=null){
				//当前页-1
				Integer page = Integer.valueOf((String)map.get("page"))-1;
				//每页多少条数据
				Integer limit = Integer.valueOf((String)map.get("limit"));
				//从哪一条就开始
				Integer size = page*limit;
				sql.append("limit "+size+" , "+limit);
			}
		}
		System.out.println(sql);
		
		DBTools db = new DBTools();
		ResultSet rs = db.getQuery(sql.toString());
		List<User> list = new ArrayList<>();
		while(rs.next()){
			User user = new User();
			user.setId(rs.getLong("id"));
			user.setCreateTime(rs.getString("create_time"));
			user.setUpdateTime(rs.getString("update_time"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setStatus(rs.getInt("status"));
			user.setPhone(rs.getString("phone"));
			list.add(user);
		}
		db.close();
		return list.size()==0?null:list;
	}

	public Integer save(User user) throws Exception{
		String sql = "insert into user(create_time,update_time,username,"
				+ "password,status,phone) value (?,?,?,?,?,?) ";
		System.out.println(sql);
		DBTools db = new DBTools();
		Connection connection = db.getCon();
		PreparedStatement pre = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//ͨ��Connection����������ֵ��PreparedStatement
		pre.setString(1,getCurrentTimeStr());
		pre.setString(2,getCurrentTimeStr());
		pre.setString(3,user.getUsername());
		pre.setString(4,user.getPassword());
		pre.setInt(5,user.getStatus());
		pre.setString(6,user.getPhone());
		pre.executeUpdate();
		ResultSet rs = pre.getGeneratedKeys();
		Integer id = null;
		if(rs.next()) {
			id = rs.getInt(1);
		}
		db.close();
        return id;
	}
	
	public Integer update(User user) throws Exception{
		String sql = "update user set update_time='"+getCurrentTimeStr()+"' ,password='"+user.getPassword()+"'"
				+ " ,phone='"+user.getPhone()+"' where id="+user.getId()+" ";
		System.out.println(sql);
		DBTools db = new DBTools();
		int res = db.update(sql);
		db.close();
        return res==0?null:res;
	}

	public Integer remove(Long id) throws Exception{
		String sql = "delete from user where id="+id+" ";
		System.out.println(sql);
		DBTools db = new DBTools();
		int res = db.update(sql);
		db.close();
        return res==0?null:res;
	}

	public static String getCurrentTimeStr(){
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-DD hh:mm:ss");
		String currentTime = simpleDateFormat.format(new Date());
		return currentTime;
	}
	
}
